package com.virtual.repository.plugins.sql;

import java.net.UnknownHostException;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import org.apache.commons.dbcp.BasicDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import com.virtual.repository.common.criteria.CriteriaBuilder;
import com.virtual.repository.common.queries.Query;
import com.virtual.repository.common.queries.QueryBuilder;
import com.virtual.repository.common.queries.ResultEntity;
import com.virtual.repository.plugins.api.IDatabasePlugin;

/**
 * Class represents MySQL plugin for repository model
 * @author kchaber
 *
 */
public class MySQLDBDriver implements IDatabasePlugin {

	/**
	 * The field holding connection to the database
	 */
	private JdbcTemplate jdbcTemplate;

	/**
	 * The URL to the database
	 */
	private String url;

	/**
	 * The password nedded to connect to the database
	 */
	private String password;

	/**
	 * The user name needed to connect to the database
	 */
	private String username;

	/**
	 * The Java driver class
	 */
	private String driverClass;

	public MySQLDBDriver() {
		this.driverClass = "com.mysql.jdbc.Driver";
	}

	@Override
	public void connect() throws UnknownHostException {
		BasicDataSource dataSource = new BasicDataSource();
		dataSource.setUrl(this.url);
		dataSource.setPassword(this.password);
		dataSource.setUsername(this.username);
		dataSource.setDriverClassName(this.driverClass);

		this.jdbcTemplate = new JdbcTemplate(dataSource);
	}

	@Override
	public void disconnect() {
		if (this.jdbcTemplate.getDataSource() == null) {
			throw new IllegalStateException("Connection to the database driver: " + this.getClass().getName() + " is null");
		}

		BasicDataSource dataSource = (BasicDataSource) this.jdbcTemplate.getDataSource();
		try {
			dataSource.close();
		} catch (SQLException e) {
			throw new IllegalStateException("Cannot disconnect database driver: " + this.getClass().getName() + " due to: " + e.getMessage());
		}
	}

	public static void main(String[] args) {
		QueryBuilder qBuilder = new QueryBuilder(MySQLBeanTest.class);
		CriteriaBuilder cb = new CriteriaBuilder();
		try {
			qBuilder.addCriteria(cb.build());
		} catch (CloneNotSupportedException e) {
			throw new RuntimeException(e);
		}


		//qBuilder.addOrderByRule("number", true);

		qBuilder.setLimit(10);

		Query query = qBuilder.build(); 
		query.setEntityName("jdvr.users");
		System.out.println(query.toString());

		MySQLDBDriver driver = new MySQLDBDriver();
		driver.url="jdbc:mysql://localhost:3306/jdvr";
		driver.username="jdvr";
		driver.password="123456";

		try {
			driver.connect();
			System.out.println(driver.executeQuery(query));
			System.out.println(driver.executeQueryForSingleResult(query));
			System.out.println(driver.executeQueryForCount(query));

		} catch (UnknownHostException e) {
			throw new RuntimeException(e);
		}finally {
			driver.disconnect();
		}
	}
	@Override
	public Collection<ResultEntity> executeQuery(Query query) {

		Connection conn =null;
		ArrayList<ResultEntity> entities = null;

		try {
			conn= jdbcTemplate.getDataSource().getConnection();
			CallableStatement statement= conn.prepareCall(buildSelect(query, false));


			ResultSet resultSet= statement.executeQuery();
			entities = new ArrayList<>();

			while (resultSet.next()) {

				entities.add(parseRow(resultSet));
			}

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return entities;
	}


	@Override
	public ResultEntity executeQueryForSingleResult(Query query) {
		Connection conn =null;
		ResultEntity entity = null;

		try {
			conn= jdbcTemplate.getDataSource().getConnection();
			CallableStatement statement= conn.prepareCall(buildSelect(query, false));


			ResultSet resultSet= statement.executeQuery();
			resultSet.first();
			entity = parseRow(resultSet);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}

		return entity;
	}

	@Override
	public long executeQueryForCount(Query query) {
		Connection conn =null;
		int count =0;

		try {
			conn= jdbcTemplate.getDataSource().getConnection();
			CallableStatement statement= conn.prepareCall(buildSelect(query, true));

			ResultSet resultSet= statement.executeQuery();
			resultSet.first();
			count= resultSet.getInt(1);

		} catch (SQLException e) {
			throw new RuntimeException(e);
		}


		return count;
	}

	private ResultEntity parseRow(ResultSet resultSet) throws SQLException {

		ResultEntity resultEntity = new ResultEntity();

		int columnCount =resultSet.getMetaData().getColumnCount();
		for (int i=0; i< columnCount; i++) {
			String columnName =resultSet.getMetaData().getColumnName(i+1);
			resultEntity.setField(columnName,resultSet.getObject(columnName));
		}

		return resultEntity;
	}

	private String buildSelect(Query query, boolean count) {
		StringBuffer buffer = new StringBuffer();
		buffer.append("SELECT ");
		if (count) {
			buffer.append(" COUNT(*) ");
		}
		else {
			if (query.getSelectedFields() != null && !query.getSelectedFields().isEmpty()) {
				for(int i = 0; i < query.getSelectedFields().size(); i++) {
					if (i !=0) {
						buffer.append(", ");
					}
					buffer.append(query.getSelectedFields().get(i));
				}
			}
			else {
				buffer.append("*");
			}
		}


		buffer.append(" FROM ").append(query.getEntityName());

		if(query.getSelectionCriteria() != null && !query.getSelectionCriteria().isEmpty()) {
			buffer.append(" WHERE ");

			for (int i = 0; i<query.getSelectionCriteria().size() ; i++) {
				buffer.append(query.getSelectionCriteria().get(i));
			}
		}
		if ( query.getLimit() != null) {
			buffer.append(" LIMIT ").append(query.getLimit());
		}

		buffer.append(";");
		return buffer.toString();
	}

	public String getUrl() {
		return url;
	}

	public void setUrl(String url) {
		this.url = url;
	}

	public String getPassword() {
		return password;
	}

	public void setPassword(String password) {
		this.password = password;
	}

	public String getUsername() {
		return username;
	}

	public void setUsername(String username) {
		this.username = username;
	}

	public String getDriverClass() {
		return driverClass;
	}

	public void setDriverClass(String driverClass) {
		this.driverClass = driverClass;
	}

}
